using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Entities;
using IDAL;

namespace SQLDBDAL
{
    public class ShipDAO:IShipDAO
    {
        private const string SQL_SELECT_SHIP = "SELECT * FROM Ship";

        private const string SQL_SELECT_BY_ID = "SELECT * FROM Ship WHERE ShipID=@ShipID";

        private const string SQL_SELECT_SHIP_BY_SHIP_VIA = "SELECT * FROM Ship WHERE ShipVia=@ShipVia";

        private const string SQL_INSERT_SHIP = "INSERT INTO Ship (ShipVia, ShipPrice) VALUES (@ShipVia, @ShipPrice)";

        private const string SQL_UPDATE_SHIP = "UPDATE Ship SET ShipVia=@ShipVia, ShipPrice=@ShipPrice WHERE ShipID=@ShipID";

        private const string SQL_DELETE_SHIP = "DELETE FROM Ship WHERE ShipID=@ShipID";

        private const string PARAM_SHIP_VIA = "@ShipVia";

        private const string PARAM_SHIP_PRICE = "@ShipPrice";

        private const string PARAM_SHIP_ID = "@ShipID";

        #region IShipDAO Members

        public IList<ShipInfo> GetAll()
        {
            IList<ShipInfo> shipIn = new List<ShipInfo>();                
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_SHIP, null))
            {
                while (reader.Read())
                {
                    ShipInfo ship = new ShipInfo();
                    ship.ShipID = reader.GetInt32(0);
                    ship.ShipVia = reader.GetString(1);
                    ship.ShipPrice = reader.GetDecimal(2);
                   
                    shipIn.Add(ship);
                }
            }
            return shipIn;
        }

        public ShipInfo GetByName(string shipVia)
        {
            SqlParameter[] parms = new SqlParameter[] { new SqlParameter(PARAM_SHIP_VIA, SqlDbType.NVarChar) };

            if (!string.IsNullOrEmpty(shipVia))
                parms[0].Value = shipVia.Trim();

            ShipInfo ship = new ShipInfo();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_SHIP_BY_SHIP_VIA, parms))
            {
                while (reader.Read())
                {                    
                    ship.ShipID = reader.GetInt32(0);
                    ship.ShipVia = reader.GetString(1);
                    ship.ShipPrice = reader.GetDecimal(2);
                }
            }
            return ship;
        }

        public ShipInfo GetByID(string shipID)
        {
            SqlParameter[] parms = new SqlParameter[] { new SqlParameter(PARAM_SHIP_ID, SqlDbType.Int) };

            if (!string.IsNullOrEmpty(shipID))
                parms[0].Value = shipID;

            ShipInfo ship = new ShipInfo();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING,
                CommandType.Text, SQL_SELECT_BY_ID, parms))
            {
                while (reader.Read())
                {
                    ship.ShipID = reader.GetInt32(0);
                    ship.ShipVia = reader.GetString(1);
                    ship.ShipPrice = reader.GetDecimal(2);
                }
            }
            return ship;
        }

        public bool Insert(ShipInfo ship)
        {
            SqlParameter[] parms = new SqlParameter[] { new SqlParameter(PARAM_SHIP_VIA, SqlDbType.NVarChar),
                                                        new SqlParameter(PARAM_SHIP_PRICE, SqlDbType.Decimal) };
            parms[0].Value = ship.ShipVia;
            parms[1].Value = ship.ShipPrice;

            try
            {
                int result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_INSERT_SHIP, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool Delete(ShipInfo ship)
        {
            SqlParameter[] parms = new SqlParameter[] { new SqlParameter(PARAM_SHIP_ID, SqlDbType.Int) };
            parms[0].Value = ship.ShipID;

            try
            {
                int result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_DELETE_SHIP, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool Update(ShipInfo ship)
        {
            SqlParameter[] parms = new SqlParameter[] { new SqlParameter(PARAM_SHIP_VIA, SqlDbType.NVarChar),
                                                        new SqlParameter(PARAM_SHIP_PRICE, SqlDbType.Decimal),
                                                        new SqlParameter(PARAM_SHIP_ID, SqlDbType.Int) };
            parms[0].Value = ship.ShipVia;
            parms[1].Value = ship.ShipPrice;
            parms[2].Value = ship.ShipID;


            try
            {
                int result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_UPDATE_SHIP, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion
    }
}
